﻿Imports System.Data
Imports System.Data.SqlClient
Imports Entidad

Public Class AgenciaDatos

    Sub registrarAgenciaTransporte(ByVal agencia As AgenciaTransporte)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "INSERT INTO AgenciaTransporte(AGE_Codigo,AGE_Ruc,AGE_Descripcion,AGE_Fecha,AGE_FechaMod,AGE_Activo) " &
                              "VALUES(@AGE,@RUC,@DES,GETDATE(),GETDATE(),@ACT)"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@AGE", agencia.AGE_Codigo)
            .Parameters.AddWithValue("@RUC", agencia.AGE_Ruc)
            .Parameters.AddWithValue("@DES", agencia.AGE_Descripcion)
            .Parameters.AddWithValue("@ACT", agencia.AGE_Activo)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Sub registrarAgenciaTransporteSucursal(ByVal agencia As AgenciaTransporteSucursal)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "INSERT INTO AgenciaTransporteSucursal(AGE_Codigo,ATS_Descripcion,ATS_Direccion,ATS_Referencia,ATS_Observacion, " &
                              "ATS_Telefono,DIS_Codigo,ATS_Latitud,ATS_Longitud,ATS_Activo) " &
                              "VALUES(@AGE,@DES,@DIR,@REF,@OBS,@TEL,@DIS,@LAT,@LON,@ACT)"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@AGE", agencia.AGE_Codigo)
            .Parameters.AddWithValue("@DES", agencia.ATS_Descripcion)
            .Parameters.AddWithValue("@DIR", agencia.ATS_Direccion)
            .Parameters.AddWithValue("@REF", agencia.ATS_Referencia)
            .Parameters.AddWithValue("@OBS", agencia.ATS_Observacion)
            .Parameters.AddWithValue("@TEL", agencia.ATS_Telefono)
            .Parameters.AddWithValue("@DIS", agencia.DIS_Codigo)
            .Parameters.AddWithValue("@LAT", agencia.ATS_Latitud)
            .Parameters.AddWithValue("@LON", agencia.ATS_Longitud)
            .Parameters.AddWithValue("@ACT", agencia.ATS_Activo)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Sub actualizarAgenciaTransporte(ByVal agencia As AgenciaTransporte)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE AgenciaTransporte " &
                              "SET AGE_Ruc = @RUC, AGE_Descripcion = @DES, " &
                              "AGE_FechaMod = GETDATE(), AGE_Activo = @ACT " &
                              "WHERE AGE_Codigo = @AGE"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@RUC", agencia.AGE_Ruc)
            .Parameters.AddWithValue("@DES", agencia.AGE_Descripcion)
            .Parameters.AddWithValue("@ACT", agencia.AGE_Activo)
            .Parameters.AddWithValue("@AGE", agencia.AGE_Codigo)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Sub actualizarAgenciaTransporteSucursal(ByVal agencia As AgenciaTransporteSucursal)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE AgenciaTransporteSucursal " &
                              "SET ATS_Descripcion = @DES, ATS_Direccion = @DIR, " &
                              "ATS_Referencia = @REF, ATS_Observacion = @OBS, " &
                              "ATS_Telefono = @TEL, DIS_Codigo = @DIS, " &
                              "ATS_Latitud = @LAT, ATS_Longitud = @LON, " &
                              "ATS_Activo = @ACT " &
                              "WHERE AGE_Codigo = @AGE AND ATS_Codigo = @COD"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@DES", agencia.ATS_Descripcion)
            .Parameters.AddWithValue("@DIR", agencia.ATS_Direccion)
            .Parameters.AddWithValue("@REF", agencia.ATS_Referencia)
            .Parameters.AddWithValue("@OBS", agencia.ATS_Observacion)
            .Parameters.AddWithValue("@TEL", agencia.ATS_Telefono)
            .Parameters.AddWithValue("@DIS", agencia.DIS_Codigo)
            .Parameters.AddWithValue("@LAT", agencia.ATS_Latitud)
            .Parameters.AddWithValue("@LON", agencia.ATS_Longitud)
            .Parameters.AddWithValue("@ACT", agencia.ATS_Activo)
            .Parameters.AddWithValue("@AGE", agencia.AGE_Codigo)
            .Parameters.AddWithValue("@COD", agencia.ATS_Codigo)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Sub registrarClienteAgenciaSucursal(codCliente As String, codSucursal As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "INSERT INTO ClienteAgenciaTransporteSucursal(CLI_Codigo,ATS_Codigo,CAT_Fecha,CAT_Activo) " &
                              "VALUES(@CLI,@ATS,GETDATE(),'1')"
        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CLI", codCliente)
            .Parameters.AddWithValue("@ATS", codSucursal)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerTodoAgencia() As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT AGE_Codigo,AGE_RUC,AGE_Descripcion,AGE_Activo " &
                              "FROM AgenciaTransporte"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function verificarExistenciaAgenciaTransporte(codAgencia As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT COUNT(*) AS Contar " &
                              "FROM AgenciaTransporte " &
                              "WHERE AGE_Codigo = @AGE"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@AGE", codAgencia)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        If CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim) > 0 Then
            valor = True
        End If

        Return valor
    End Function

    Function verificarExistenciaAgenciaTransporteSucursal(codAgencia As String, codigo As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT COUNT(*) AS Contar " &
                              "FROM AgenciaTransporteSucursal " &
                              "WHERE AGE_Codigo = @AGE " &
                              "AND ATS_Codigo = @COD"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@AGE", codAgencia)
            .Parameters.AddWithValue("@COD", codigo)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        If CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim) > 0 Then
            valor = True
        End If

        Return valor
    End Function

    Function obtenerSucursalesAgencia(codAgencia As String, flag As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT ATS_Codigo, ATS_Descripcion, ATS_Direccion, ATS_Referencia, ATS_Observacion, ATS_Telefono, DIS_Codigo, " &
                              "ATS_Longitud, ATS_Latitud, ATS_Activo " &
                              "FROM dbo.AgenciaTransporteSucursal " &
                              "WHERE AGE_Codigo = '" & codAgencia & "' "

        If flag <> "1" Then
            query = query & "AND ATS_Activo = '1'"
        End If

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function verificarExistenciaRuc(rucN As String, rucA As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT COUNT(*) AS Contar " &
                              "FROM AgenciaTransporte " &
                              "WHERE AGE_Ruc = @RUC1 " &
                              "AND AGE_RUC <> @RUC2 AND LEN(RTRIM(LTRIM(AGE_Ruc))) > 0"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@RUC1", rucN)
            .Parameters.AddWithValue("@RUC2", rucA)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim) > 0 Then
            valor = True
        End If

        Return valor
    End Function

    Sub registrarDestinos(codSucursal As Integer, codProvincia As String, codDistrito As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "USP_RegistrarDestinos"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .CommandType = CommandType.StoredProcedure
            .Parameters.AddWithValue("@ATSCOD", codSucursal)
            .Parameters.AddWithValue("@PROVCOD", codProvincia)
            .Parameters.AddWithValue("@DISCOD", codDistrito)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Function listarDestinosSucursal(codSucursal As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT ats.ATSD_Codigo, LTRIM(RTRIM(d.DIS_Descripcion)) AS Descripcion " &
                              "FROM AgenciaTSDestinos ats " &
                              "INNER JOIN Distrito d " &
                              "ON d.DIS_Codigo = ats.DIS_Codigo AND d.DIS_Activo = '1' " &
                              "WHERE ats.ATS_Codigo = @ATS AND ats.ATSD_Activo = '1' " &
                              "ORDER BY d.DIS_Descripcion"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@ATS", codSucursal)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub eliminarDestinoSucursal(codATSD As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "DELETE AgenciaTSDestinos WHERE ATSD_Codigo = @ATSD"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@ATSD", codATSD)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()
    End Sub

End Class
